package excel

import (
	"gin-swagger-demo/pkg/setting"
	"github.com/astaxie/beego"
	"github.com/xuri/excelize/v2"
	"strconv"
)


// GetExcelFullUrl get the full access path of the Excel file
func GetExcelFullUrl(name string) string {
	return setting.AppSetting.PrefixUrl + "/" + GetExcelPath() + name
}

// GetExcelPath get the relative save path of the Excel file
func GetExcelPath() string {
	return setting.AppSetting.ExportSavePath
}

// GetExcelFullPath Get the full save path of the Excel file
func GetExcelFullPath() string {
	return setting.AppSetting.RuntimeRootPath + GetExcelPath()
}

/*
GetExcelTemplate 动态获取excel模板
filename excel文件名称
des excel注意或描述
title excel有效列表头
length 总列数
*/
func GetExcelTemplate(filename string, des, title interface{}, length int) (string, error) {
	f := excelize.NewFile()
	index := f.NewSheet("Sheet1")
	// style
	style, _ := f.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{
			Vertical: "center",
			WrapText: true,
		},
		Font: &excelize.Font{
			Size: 15,
			Bold: true,
		},
	})
	// 备注或注意描述 首行合并多列，定义样式
	f.SetSheetRow("Sheet1", "A1", des)
	f.MergeCell("Sheet1", "A1", GetExcelColumnLetterByNumber(length, 1))
	f.SetCellStyle("Sheet1", "A1", "A1", style)
	f.SetRowHeight("Sheet1", 1, 140)
	f.SetColWidth("Sheet1", "A", "Z", 28)

	// 各列表头设置
	f.SetSheetRow("Sheet1", "A2", title)

	f.SetActiveSheet(index)

	filePath := GetExcelPath() + filename
	err := f.SaveAs(filePath)
	if err != nil {
		beego.Debug("导出excel文件失败", err)
		return "", err
	}
	return filePath, nil
}

// 数字转化为excel列标识 如 A1 B2
func GetExcelColumnLetterByNumber(number, row int) string {
	letterMap := map[int]string{
		1: "A", 2: "B", 3: "C", 4: "D", 5: "E",
		6: "F", 7: "G", 8: "H", 9: "I", 10: "J",
		11: "K", 12: "L", 13: "M", 14: "N",
		15: "O", 16: "P", 17: "Q", 18: "R",
		19: "S", 20: "T", 21: "U", 22: "V",
		23: "W", 24: "X", 25: "Y", 26: "Z",
	}
	key := number % 26
	if end, ok := letterMap[key]; ok {
		return end + strconv.Itoa(row)
	}
	return ""
}


// GetExcelContent 获取excel中的内容
func GetExcelContent(filePath string) ([][]string, error) {
	var content [][]string
	// 读取excel文件
	excelFile, err := excelize.OpenFile(filePath)
	if err != nil {
		return content, err
	}
	// 迭代数据
	rows, err := excelFile.Rows("Sheet1")
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		row, err := rows.Columns()
		if err != nil {
			return content, err
		}
		content = append(content, row)
	}
	return content, nil
}

// SetExcelContent 设置excel内容数据 到处excel
func SetExcelContent(filename string, title *[]interface{}, content *[][]interface{}) (string, error) {
	f := excelize.NewFile()
	// 创建一个工作表
	index := f.NewSheet("Sheet1")
	// 生成第一行数据
	f.SetSheetRow("Sheet1", "A1", title)
	// 依次传入数据 行+2
	for k, v := range *content {
		rowNum := k + 2
		err := f.SetSheetRow("Sheet1", "A"+strconv.Itoa(rowNum), &v)
		if err != nil {
			beego.Debug("写入excel数据失败", err)
			return "", err
		}
	}
	f.SetActiveSheet(index)
	filePath := GetExcelPath() + filename
	err := f.SaveAs(filePath)
	if err != nil {
		beego.Debug("导出excel文件失败", err)
		return "", err
	}
	return filePath, nil
}